package cn.hpclub.server.service;

import cn.hpclub.server.util.excel.ExportUtils;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import org.apache.poi.xssf.streaming.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.List;

public class ApiLogService {
    private static final Logger logger = LoggerFactory.getLogger(ApiLogService.class);
    public static final ApiLogService service = new ApiLogService();
    public static final DecimalFormat dfPercent = new DecimalFormat("#0.00");//#表示有就有，没有就没有，0是一定占位

    private String apiTimeRange() {
        String sql = "SELECT t.api AS api, COUNT(*) AS total," +//接口名//总调用数
                "COUNT( CASE WHEN t.success != 1 THEN t.success END ) AS apiFailed," +//接口失败数
                "COUNT( CASE WHEN t.success = 1 THEN t.success END ) AS apiSuccess," +//接口成功数
                "COUNT( CASE WHEN t.business_success != 0 THEN t.business_success END ) AS businessFailed," +//业务失败数
                "COUNT( CASE WHEN t.business_success = 0 THEN t.business_success END ) AS businessSuccess," +//业务成功数
//                "apiFailed/total AS apiRate,"+//业务失败率
//                "businessFailed/total AS businessRate,"+//接口失败率
                "MAX(t.last_time) AS maxLastTime," +//最大响应时长
                "MIN(t.last_time) AS minLastTime," +//最小响应时长
                "AVG(t.last_time) AS avgLastTime," +//平均响应时长
                "COUNT( CASE WHEN t.last_time <= 1000 THEN t.last_time END ) AS less1000," +//<1000
                "COUNT( CASE WHEN t.last_time > 1000 AND t.last_time <= 3000 THEN t.last_time END ) AS in10003000," +//1000-3000
                "COUNT( CASE WHEN t.last_time > 3000 AND t.last_time <= 5000 THEN t.last_time END ) AS in30005000," +//3000-5000
                "COUNT( CASE WHEN t.last_time > 5000 AND t.last_time <= 10000 THEN t.last_time END ) AS in500010000," +//5000-10000
                "COUNT( CASE WHEN t.last_time > 10000 AND t.last_time <= 15000 THEN t.last_time END ) AS in1000015000," +//10000-15000
                "COUNT( CASE WHEN t.last_time > 15000 THEN t.last_time END ) AS more15000 " +//>15000
                "FROM transaction_log t WHERE t.api=? AND t.opt_time>? AND t.opt_time<=?";
        return sql;
    }

    public Record queryTimeRange(String api, String time_start, String time_end) {
        String sql = apiTimeRange();
        return Db.findFirst(sql, api, time_start, time_end);
    }

    private String apiTimeRangeByDay() {
        String sql = "SELECT DATE_FORMAT(t.opt_time,'%Y-%m-%d') AS opt_time,t.api AS api," +
                "COUNT(t.success) AS total," +
                "COUNT( CASE WHEN t.success != 1 THEN t.success END ) AS apiFailed," +
                "COUNT( CASE WHEN t.success = 1 THEN t.success END ) AS apiSuccess," +
                "COUNT( CASE WHEN t.business_success != 0 THEN t.business_success END ) AS businessFailed," +
                "COUNT( CASE WHEN t.business_success = 0 THEN t.business_success END ) AS businessSuccess " +
                "FROM transaction_log t WHERE t.api=? AND t.opt_time >=? AND t.opt_time <=?  GROUP BY DATE_FORMAT(t.opt_time,'%Y-%m-%d')";
        return sql;
    }

    /**
     * 按天查询每天的调用情况
     *
     * @param api        接口名
     * @param time_start 2017-08-24
     * @param time_end   2017-08-24
     * @return 查询结果
     */
    public List<Record> queryTimeRangeByDay(String api, String time_start, String time_end) {
        String sql = apiTimeRangeByDay();
        time_start = time_start + " 00:00:00";
        time_end = time_end + " 23:59:59";
        List<Record> records = Db.find(sql, api, time_start, time_end);
        for (Record record : records) {
            double denominator = Double.parseDouble(record.getLong("total") + "");
            denominator = (0 == denominator) ? 1 : denominator;//防止为0
            double apiFailed = Double.parseDouble(record.getLong("apiFailed") + "");
            double apiFailedPercent = apiFailed / denominator * 100;
            record.set("apiFailedPercent", dfPercent.format(apiFailedPercent)+"%");
            double apiSuccess = Double.parseDouble(record.getLong("apiSuccess") + "");
            double apiSuccessPercent = apiSuccess / denominator * 100;
            record.set("apiSuccessPercent", dfPercent.format(apiSuccessPercent)+"%");
            double businessFailed = Double.parseDouble(record.getLong("businessFailed") + "");
            double businessFailedPercent = businessFailed / denominator * 100;
            record.set("businessFailedPercent", dfPercent.format(businessFailedPercent)+"%");
            double businessSuccess = Double.parseDouble(record.getLong("businessSuccess") + "");
            double businessSuccessPercent = businessSuccess / denominator * 100;
            record.set("businessSuccessPercent", dfPercent.format(businessSuccessPercent)+"%");
        }
        return records;
    }

    public SXSSFWorkbook getExcel(String api, String time_start, String time_end) {
        SXSSFWorkbook workbook = null;
        List<Record> seq = queryTimeRangeByDay(api, time_start, time_end);
        String[] excelHeaders = new String[]{"时间", "接口名", "总共调用次数", "接口失败数", "接口成功数", "接口失败率", "业务成功数","业务失败数", "业务失败率"};
        ExportUtils exportUtils = new ExportUtils(excelHeaders);
        String[] keys = new String[]{"opt_time", "api", "total", "apiSuccess", "apiFailed", "apiFailedPercent", "businessSuccess", "businessFailed", "businessFailedPercent"};
        try {
            exportUtils.exportExcel(keys, seq, "yyyy-MM-dd HH:ss:mm", 1);// 此处最后一个参数表示从第一行开始填入数据（表头为第0行）
            workbook = exportUtils.getSXSSFWorkbook();
        } catch (FileNotFoundException e) {
            logger.error("TransactionLogService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an FileNotFoundException,error message:"
                    + e.getMessage());
        } catch (IOException ex) {
            logger.error("TransactionLogService.getExcel(ConcurrentHashMap<String, String> searchInfo) happened an IOException,error message:"
                    + ex.getMessage());
        }
        return workbook;
    }

    private String apiChartsSQL() {
        String sql = "SELECT t.api AS api, COUNT(*) AS total," +//接口名//总调用数
                "(UNIX_TIMESTAMP(t.opt_time)*1000+8*3600*1000) AS opt_time," +//时区的问题
                "COUNT( CASE WHEN t.success != 1 THEN t.success END ) AS apiFailed," +//接口失败数
                "COUNT( CASE WHEN t.success = 1 THEN t.success END ) AS apiSuccess," +//接口成功数
                "COUNT( CASE WHEN t.business_success != 0 THEN t.business_success END ) AS businessFailed," +//业务失败数
                "COUNT( CASE WHEN t.business_success = 0 THEN t.business_success END ) AS businessSuccess, " +//业务成功数
                "MAX(t.last_time) AS maxLastTime," +//最大响应时长
                "MIN(t.last_time) AS minLastTime," +//最小响应时长
                "AVG(t.last_time) AS avgLastTime," +//平均响应时长
                "COUNT( CASE WHEN t.last_time <= 1000 THEN t.last_time END ) AS less1000," +//<1000
                "COUNT( CASE WHEN t.last_time > 1000 AND t.last_time <= 3000 THEN t.last_time END ) AS in10003000," +//1000-3000
                "COUNT( CASE WHEN t.last_time > 3000 AND t.last_time <= 5000 THEN t.last_time END ) AS in30005000," +//3000-5000
                "COUNT( CASE WHEN t.last_time > 5000 AND t.last_time <= 10000 THEN t.last_time END ) AS in500010000," +//5000-10000
                "COUNT( CASE WHEN t.last_time > 10000 AND t.last_time <= 15000 THEN t.last_time END ) AS in1000015000," +//10000-15000
                "COUNT( CASE WHEN t.last_time > 15000 THEN t.last_time END ) AS more15000 " +//>15000
                "FROM transaction_log t WHERE t.api=? AND t.opt_time>? AND t.opt_time<=? GROUP BY DATE_FORMAT(t.opt_time,'%Y-%m-%d %H:%i') ORDER BY opt_time";
        return sql;
    }
    public List<Record> getChartsData(String api, String time_start, String time_end){
        String sql = apiChartsSQL();
        return  Db.find(sql,api, time_start, time_end);
    }

    private long countAll(String api, String time_start, String time_end){
        String sql = "SELECT COUNT(*) AS countAll FROM transaction_log t WHERE t.api = ? AND t.opt_time >= ? AND t.opt_time <= ?";
        return  Db.findFirst(sql,api,time_start,time_end).getLong("countAll");
    }
    public List<Record> codeCount(String api, String time_start, String time_end){
        String sql = "SELECT t.api AS api, COUNT(*) AS retcode_count," +
                "DATE_FORMAT(MAX(t.opt_time),'%Y-%m-%d %H:%i') AS max_time," +
                "DATE_FORMAT(MIN(t.opt_time),'%Y-%m-%d %H:%i') AS min_time," +
                "return_code,boss_output " +
                "FROM transaction_log t WHERE t.api = ? AND t.opt_time >= ? AND t.opt_time <= ? GROUP BY return_code";
        List<Record> records = Db.find(sql,api,time_start,time_end);
        long countAll = countAll(api, time_start, time_end);
        for (Record record:records){
            record.set("percent", dfPercent.format(100.0*record.getLong("retcode_count")/countAll));
        }
        return  records;
    }
}
